using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Windows.Forms;
using SqlSugar;
using twistmachinev2.Entity;

namespace twistmachinev2.Repository
{
    /// <summary>
    /// OPCUA数据库操作仓储类
    /// </summary>
    public class OPCUARepository : IDisposable
    {
        private SqlSugarClient _db;
        private bool _isConnected = false;
        private bool _disposed = false;

        /// <summary>
        /// 默认构造函数
        /// </summary>
        public OPCUARepository()
        {
            // 使用BaseData中配置的数据库连接字符串
            string connectionString = BaseData.OPCUADatabaseConnectionString;
            InitializeDatabase(connectionString);
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        public OPCUARepository(string connectionString)
        {
            InitializeDatabase(connectionString);
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbPath">数据库文件路径</param>
        public OPCUARepository(string dbPath, bool isFilePath)
        {
            if (isFilePath)
            {
                string connectionString = $"Data Source={dbPath}";
                InitializeDatabase(connectionString);
            }
            else
            {
                InitializeDatabase(dbPath);
            }
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        private void InitializeDatabase(string connectionString)
        {
            try
            {
                // 确保数据库目录存在
                string dbPath = ExtractDatabasePath(connectionString);
                if (!string.IsNullOrEmpty(dbPath))
                {
                    string directory = Path.GetDirectoryName(dbPath);
                    if (!string.IsNullOrEmpty(directory) && !Directory.Exists(directory))
                    {
                        Directory.CreateDirectory(directory);
                    }
                }

                _db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = connectionString,
                    DbType = SqlSugar.DbType.Sqlite,
                    IsAutoCloseConnection = true,
                    InitKeyType = InitKeyType.Attribute
                });

                // 测试连接
                _db.Ado.CheckConnection();
                _isConnected = true;
            }
            catch (Exception ex)
            {
                // 获取数据库文件路径用于错误提示
                string dbPath = ExtractDatabasePath(connectionString);
                string errorMessage = $"数据库连接失败: {ex.Message}\n连接字符串: {connectionString}";
                if (!string.IsNullOrEmpty(dbPath))
                {
                    errorMessage += $"\n数据库文件路径: {dbPath}";
                    errorMessage += $"\n文件是否存在: {File.Exists(dbPath)}";
                }
                throw new InvalidOperationException(errorMessage, ex);
            }
        }

        /// <summary>
        /// 从连接字符串中提取数据库文件路径
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>数据库文件路径</returns>
        private string ExtractDatabasePath(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                return null;

            try
            {
                var parts = connectionString.Split(';');
                foreach (var part in parts)
                {
                    if (part.Trim().StartsWith("Data Source=", StringComparison.OrdinalIgnoreCase))
                    {
                        return part.Substring("Data Source=".Length).Trim();
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine($"提取数据库文件路径失败: {ex.Message}");
            }

            return null;
        }

        #region 连接管理

        /// <summary>
        /// 检查数据库连接状态
        /// </summary>
        public bool IsConnected => _isConnected && _db != null && !_disposed;

        /// <summary>
        /// 获取SqlSugar客户端实例
        /// </summary>
        /// <returns>SqlSugar客户端</returns>
        public SqlSugarClient GetDbClient()
        {
            CheckConnection();
            return _db;
        }

        /// <summary>
        /// 检查连接状态
        /// </summary>
        private void CheckConnection()
        {
            if (!IsConnected)
            {
                throw new InvalidOperationException("数据库未连接或连接已断开");
            }
        }

        #endregion

        #region 数据库诊断方法

        /// <summary>
        /// 获取数据库中所有表的名称（用于诊断）
        /// </summary>
        /// <returns>表名列表</returns>
        public List<string> GetAllTableNames()
        {
            CheckConnection();
            try
            {
                var sql = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;";
                var tables = _db.Ado.SqlQuery<string>(sql);
                return tables;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"获取表名列表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 检查指定表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>表是否存在</returns>
        public bool TableExists(string tableName)
        {
            CheckConnection();
            try
            {
                var sql = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=@tableName;";
                var count = _db.Ado.SqlQuery<int>(sql, new { tableName }).FirstOrDefault();
                return count > 0;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"检查表是否存在失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取数据库文件信息（用于诊断）
        /// </summary>
        /// <returns>数据库信息</returns>
        public string GetDatabaseInfo()
        {
            CheckConnection();
            try
            {
                var connectionString = _db.CurrentConnectionConfig.ConnectionString;
                var dbPath = ExtractDatabasePath(connectionString);
                var fileExists = !string.IsNullOrEmpty(dbPath) && System.IO.File.Exists(dbPath);
                var fileSize = fileExists ? new System.IO.FileInfo(dbPath).Length : 0;
                
                var info = $"连接字符串: {connectionString}\n";
                info += $"数据库文件路径: {dbPath}\n";
                info += $"文件是否存在: {fileExists}\n";
                info += $"文件大小: {fileSize} 字节\n";
                
                if (fileExists)
                {
                    var tables = GetAllTableNames();
                    info += $"表数量: {tables.Count}\n";
                    info += $"表列表: {string.Join(", ", tables)}";
                }
                
                return info;
            }
            catch (Exception ex)
            {
                return $"获取数据库信息失败: {ex.Message}";
            }
        }

        #endregion

        #region OPCUA配置管理

        /// <summary>
        /// 初始化OPCUA配置表
        /// </summary>
        public void InitializeOpcUaTables()
        {
            CheckConnection();
            try
            {
                // 创建服务器配置表
                _db.CodeFirst.InitTables<OpcUaServerConfigEntity>();
                // 创建节点配置表
                _db.CodeFirst.InitTables<OpcUaNodeConfigEntity>();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"初始化OPCUA配置表失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取OPCUA服务器配置
        /// </summary>
        /// <returns>服务器配置</returns>
        public OpcUaServerConfigEntity GetOpcUaServerConfig()
        {
            CheckConnection();
            try
            {
                return _db.Queryable<OpcUaServerConfigEntity>()
                    .Where(x => x.IsEnabled)
                    .OrderBy(x => x.Id)
                    .First();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"获取OPCUA服务器配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存或更新OPCUA服务器配置
        /// </summary>
        /// <param name="config">服务器配置</param>
        /// <returns>操作成功返回true</returns>
        public bool SaveOpcUaServerConfig(OpcUaServerConfigEntity config)
        {
            CheckConnection();
            try
            {
                config.UpdateTime = DateTime.Now;
                if (config.Id > 0)
                {
                    return _db.Updateable(config).ExecuteCommand() > 0;
                }
                else
                {
                    config.CreateTime = DateTime.Now;
                    return _db.Insertable(config).ExecuteCommand() > 0;
                }
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"保存OPCUA服务器配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 获取所有OPCUA节点配置
        /// </summary>
        /// <returns>节点配置列表</returns>
        public List<OpcUaNodeConfigEntity> GetAllOpcUaNodeConfigs()
        {
            CheckConnection();
            try
            {
                return _db.Queryable<OpcUaNodeConfigEntity>()
                    .Where(x => x.IsEnabled)
                    .OrderBy(x => x.Sort)
                    .ToList();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"获取OPCUA节点配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据分组获取OPCUA节点配置
        /// </summary>
        /// <param name="group">分组名称</param>
        /// <returns>节点配置列表</returns>
        public List<OpcUaNodeConfigEntity> GetOpcUaNodeConfigsByGroup(string group)
        {
            CheckConnection();
            try
            {
                return _db.Queryable<OpcUaNodeConfigEntity>()
                    .Where(x => x.IsEnabled && x.Group == group)
                    .OrderBy(x => x.Sort)
                    .ToList();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"根据分组获取OPCUA节点配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 根据NodeId获取OPCUA节点配置
        /// </summary>
        /// <param name="nodeId">节点ID</param>
        /// <returns>节点配置</returns>
        public OpcUaNodeConfigEntity GetOpcUaNodeConfigByNodeId(string nodeId)
        {
            CheckConnection();
            try
            {
                return _db.Queryable<OpcUaNodeConfigEntity>()
                    .Where(x => x.IsEnabled && x.NodeId == nodeId)
                    .First();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"根据NodeId获取OPCUA节点配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 保存或更新OPCUA节点配置
        /// </summary>
        /// <param name="config">节点配置</param>
        /// <returns>操作成功返回true</returns>
        public bool SaveOpcUaNodeConfig(OpcUaNodeConfigEntity config)
        {
            CheckConnection();
            try
            {
                config.UpdateTime = DateTime.Now;
                if (config.Id > 0)
                {
                    return _db.Updateable(config).ExecuteCommand() > 0;
                }
                else
                {
                    config.CreateTime = DateTime.Now;
                    return _db.Insertable(config).ExecuteCommand() > 0;
                }
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"保存OPCUA节点配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 批量保存OPCUA节点配置
        /// </summary>
        /// <param name="configs">节点配置列表</param>
        /// <returns>保存成功的行数</returns>
        public int SaveOpcUaNodeConfigs(List<OpcUaNodeConfigEntity> configs)
        {
            CheckConnection();
            try
            {
                var now = DateTime.Now;
                foreach (var config in configs)
                {
                    config.UpdateTime = now;
                    if (config.Id == 0)
                    {
                        config.CreateTime = now;
                    }
                }
                return _db.Insertable(configs).ExecuteCommand();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"批量保存OPCUA节点配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 删除OPCUA节点配置
        /// </summary>
        /// <param name="id">配置ID</param>
        /// <returns>删除成功返回true</returns>
        public bool DeleteOpcUaNodeConfig(int id)
        {
            CheckConnection();
            try
            {
                return _db.Deleteable<OpcUaNodeConfigEntity>().In(id).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"删除OPCUA节点配置失败: {ex.Message}", ex);
            }
        }

        /// <summary>
        /// 从JSON配置文件迁移数据到数据库
        /// </summary>
        /// <param name="jsonFilePath">JSON文件路径</param>
        /// <returns>迁移成功返回true</returns>
        public bool MigrateFromJsonConfig(string jsonFilePath)
        {
            CheckConnection();
            try
            {
                if (!System.IO.File.Exists(jsonFilePath))
                {
                    throw new FileNotFoundException($"配置文件不存在: {jsonFilePath}");
                }

                string jsonContent = System.IO.File.ReadAllText(jsonFilePath);
                var configRoot = Newtonsoft.Json.JsonConvert.DeserializeObject<OpcUaConfigRoot>(jsonContent);

                if (configRoot == null)
                {
                    throw new InvalidOperationException("JSON配置文件格式错误");
                }

                // 初始化表
                InitializeOpcUaTables();

                // 迁移服务器配置
                var serverConfig = new OpcUaServerConfigEntity
                {
                    ServerUrl = configRoot.ServerUrl,
                    Username = configRoot.Username,
                    Password = configRoot.Password,
                    IsEnabled = true,
                    CreateTime = DateTime.Now,
                    UpdateTime = DateTime.Now,
                    Remark = "从JSON配置文件迁移"
                };
                SaveOpcUaServerConfig(serverConfig);

                // 迁移节点配置
                var nodeConfigs = new List<OpcUaNodeConfigEntity>();
                int sort = 1;
                foreach (var node in configRoot.Nodes)
                {
                    var nodeConfig = new OpcUaNodeConfigEntity
                    {
                        NodeId = node.NodeId,
                        Name = node.Name,
                        Description = node.Description,
                        DataType = node.DataType,
                        AccessLevel = node.AccessLevel,
                        ScanRate = node.ScanRate,
                        IsSubscribed = node.IsSubscribed,
                        PropertyName = node.PropertyName,
                        ObjectName = node.ObjectName,
                        Group = node.Group,
                        IsEnabled = true,
                        Sort = sort++,
                        CreateTime = DateTime.Now,
                        UpdateTime = DateTime.Now,
                        Remark = "从JSON配置文件迁移"
                    };
                    nodeConfigs.Add(nodeConfig);
                }

                SaveOpcUaNodeConfigs(nodeConfigs);
                return true;
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"从JSON配置文件迁移失败: {ex.Message}", ex);
            }
        }

        #endregion

        #region IDisposable实现

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="disposing">是否释放托管资源</param>
        protected virtual void Dispose(bool disposing)
        {
            if (!_disposed)
            {
                if (disposing)
                {
                    _db?.Dispose();
                    _db = null;
                    _isConnected = false;
                }
                _disposed = true;
            }
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~OPCUARepository()
        {
            Dispose(false);
        }

        #endregion
    }
}
